/*
 * $RCSfile: SqlPlus.java,v $
 * $Revision: 1.1 $
 * $Date: 2009-02-16 $
 *
 * Copyright (C) 2008 Skin, Inc. All rights reserved.
 *
 * This software is the proprietary information of Skin, Inc.
 * Use is subject to license terms.
 */
package com.skin.webcat.database.sql;

import java.io.BufferedReader;
import java.io.Closeable;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.PrintWriter;
import java.io.Reader;
import java.io.StringReader;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import com.skin.webcat.database.Column;
import com.skin.webcat.datasource.ConnectionManager;
import com.skin.webcat.util.Jdbc;

/**
 * <p>Title: SqlPlus</p>
 * <p>Description: </p>
 * <p>Copyright: Copyright (c) 2006</p>
 * @author xuesong.net
 * @version 1.0
 */
public class SqlPlus {
    private Map<String, Object> context;
    private PrintWriter out = null;

    /**
     * @param args
     */
    public static void main(String[] args) {
        SqlPlus sqlPlus = new SqlPlus(null, new PrintWriter(System.out, true));

        try {
            sqlPlus.run(new File("src\\test\\resource\\test.sql"), "utf-8");
        }
        catch (Exception e) {
        }
    }

    /**
     * default
     */
    public SqlPlus() {
        this((Map<String, Object>)null, (PrintWriter)null);
    }

    /**
     * @param context
     * @param out
     */
    public SqlPlus(Map<String, Object> context, PrintWriter out) {
        this.context = new HashMap<String, Object>();
        this.out = out;

        if(context != null) {
            this.context.putAll(context);
        }
    }

    /**
     * @param sql
     * @throws Exception
     */
    public void run(String sql) throws Exception {
        run(new StringReader(sql));
    }

    /**
     * @param sql
     * @throws Exception
     */
    public void run(StringBuilder sql) throws Exception {
        run(new StringReader(sql.toString()));
    }

    /**
     * @param file
     * @param charset
     * @throws Exception
     */
    public void run(File file, String charset) throws Exception {
        InputStream inputStream = null;

        try {
            inputStream = new FileInputStream(file);
            this.run(new InputStreamReader(inputStream, charset));
        }
        finally {
            close(inputStream);
        }
    }

    /**
     * @param inputStream
     * @param charset
     * @throws Exception
     */
    public void run(InputStream inputStream, String charset) throws Exception {
        this.run(new InputStreamReader(inputStream, charset));
    }

    /**
     * @param reader
     * @throws Exception
     */
    public void run(Reader reader) throws Exception {
        String command = null;
        BufferedReader bufferedReader = null;
        StringBuilder buffer = new StringBuilder();
        Connection connection = null;
        this.info("run");

        try {
            bufferedReader = getBufferedReader(reader);

            while((command = getCommand(bufferedReader, buffer)) != null) {
                /**
                 * 获取数据库连接
                 */
                if(command.startsWith("use ")) {
                    this.info(command);
                    close(connection);
                    connection = ConnectionManager.getConnection(command.substring(4));
                    continue;
                }

                if(command.startsWith("using ")) {
                    this.info(command);
                    close(connection);
                    connection = ConnectionManager.getConnection(command.substring(6));
                    continue;
                }

                if(command.startsWith("echo ")) {
                    this.info(command.substring(4));
                    continue;
                }

                /**
                 * 执行其他sql文件
                 * start create.sql;
                 */
                if(command.startsWith("start ")) {
                    /**
                     * 暂不支持
                     * close(connection);
                     * this.run(getReader(line.substring(6, line.length() - 1)));
                     */
                    continue;
                }

                if(command.equals("connect ")) {
                    /**
                     * 暂不支持
                     * close(connection);
                     * connection = ConnectionManager.connect(command.substring(4));
                     */
                    continue;
                }

                if(command.equals("disconnect")) {
                    this.info("disconnect;");
                    close(connection);
                    connection = null;
                    continue;
                }

                if(command.equals("commit")) {
                    this.info("commit;");
                    commit(connection);
                    continue;
                }

                if(command.equals("rollback")) {
                    this.info("rollback;");
                    rollback(connection);
                    continue;
                }

                String sql = replace(command, this.context);
                int rows = update(connection, sql);
                this.info("effect: " + rows + ", " + sql);
            }
        }
        finally {
            close(connection);
        }
    }

    /**
     * @param content
     */
    public void info(String content) {
        if(this.out != null) {
            this.out.println("[sql] " + content);
        }
    }

    /**
     * @return the context
     */
    public Map<String, Object> getContext() {
        return this.context;
    }

    /**
     * @param context the context to set
     */
    public void setContext(Map<String, Object> context) {
        this.context.clear();
        this.context.putAll(context);
    }

    /**
     * @param name
     * @param value
     */
    public void setAttribute(String name, Object value) {
        this.context.put(name, value);
    }

    /**
     * @param connection
     * @param sql
     * @return SqlResult
     * @throws SQLException 
     */
    public static SqlResult select(Connection connection, String sql) throws SQLException {
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);
            return getSqlResult(connection, resultSet);
        }
        finally {
            Jdbc.close(resultSet);
            Jdbc.close(statement);
        }
    }

    /**
     * @param connection
     * @param sql
     * @return int
     * @throws SQLException
     */
    public static int update(Connection connection, String sql) throws SQLException {
        Statement statement = null;

        try {
            statement = connection.createStatement();
            statement.execute(sql);

            return statement.executeUpdate(sql);
        }
        finally {
            close(statement);
        }
    }

    /**
     * @param connection
     * @param resultSet
     * @return SqlResult
     * @throws SQLException
     */
    public static SqlResult getSqlResult(Connection connection, ResultSet resultSet) throws SQLException {
        ResultSetMetaData medaData = resultSet.getMetaData();
        List<String> tableList = getTableNameList(medaData);
        List<Column> columns = getColumns(medaData);
        List<Record> records = getRecords(resultSet, 200);
        String tableName = tableList.get(0);
        boolean editable = (tableList.size() == 1);

        if(editable) {
            DatabaseMetaData metaData = connection.getMetaData();
            List<String> primayKeyList = getPrimaryKeys(metaData, tableName);

            for(Column column : columns) {
                if(primayKeyList.contains(column.getColumnName())) {
                    column.setPrimaryKey(true);
                }
            }
        }

        SqlResult sqlResult = SqlResult.success("success");
        sqlResult.setTableName(tableName);
        sqlResult.setEditable(editable);
        sqlResult.setColumns(columns);
        sqlResult.setRecords(records);
        return sqlResult;
    }

    /**
     * @param medaData
     * @return List<Column>
     * @throws SQLException
     */
    private static List<Column> getColumns(ResultSetMetaData medaData) throws SQLException {
        int columnCount = medaData.getColumnCount();
        List<Column> columns = new ArrayList<Column>();

        for(int i = 1; i <= columnCount; i++) {
            Column column = new Column();
            String columnName = medaData.getColumnName(i);
            int dataType = medaData.getColumnType(i);
            String typeName = medaData.getColumnTypeName(i);
            boolean autoIncrement = medaData.isAutoIncrement(i);

            column.setColumnCode(columnName);
            column.setColumnName(columnName);
            column.setDataType(dataType);
            column.setTypeName(typeName);
            column.setJavaTypeName(getJavaTypeName(dataType));
            column.setAutoIncrement(autoIncrement);
            columns.add(column);
        }
        return columns;
    }

    /**
     * @param medaData
     * @return List<String>
     * @throws SQLException 
     */
    private static List<String> getTableNameList(ResultSetMetaData medaData) throws SQLException {
        int count = medaData.getColumnCount();
        Set<String> tableNameSet = new HashSet<String>();
        List<String> tableNameList = new ArrayList<String>();

        for(int i = 1; i <= count; i++) {
            tableNameSet.add(medaData.getTableName(i));
        }
        tableNameList.addAll(tableNameSet);
        return tableNameList;
    }

    /**
     * @param metaData
     * @param tableName
     * @return List<String>
     * @throws SQLException
     */
    public static List<String> getPrimaryKeys(DatabaseMetaData metaData, String tableName) throws SQLException {
        ResultSet resultSet = null;
        List<String> primaryKeys = new ArrayList<String>();

        try {
            resultSet = metaData.getPrimaryKeys(null, null, tableName);

            while(resultSet.next()) {
                String columnName = resultSet.getString("COLUMN_NAME");
                primaryKeys.add(columnName);
            }
        }
        finally {
            close(resultSet);
        }
        return primaryKeys;
    }

    /**
     * @param medaData
     * @return List<Column>
     * @throws SQLException
     */
    private static List<Record> getRecords(ResultSet resultSet, int size) throws SQLException {
        int rows = 0;
        List<Record> records = new ArrayList<Record>();
        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();

        while(resultSet.next()) {
            List<Object> values = new ArrayList<Object>();

            for(int i = 1; i <= columnCount; i++) {
                Object value = resultSet.getObject(i);

                if(resultSet.wasNull()) {
                    values.add(null);
                }
                else {
                    values.add(value);
                }
            }
            records.add(new Record(values));
            rows++;

            if(rows >= size) {
                break;
            }
        }
        return records;
    }

    /**
     * @param bufferedReader
     * @param buffer
     * @return String
     * @throws IOException
     */
    public static String getCommand(BufferedReader bufferedReader, StringBuilder buffer) throws IOException {
        String line = null;
        buffer.setLength(0);

        while((line = bufferedReader.readLine()) != null) {
            line = line.trim();

            if(line.length() < 0 || line.startsWith("--")) {
                continue;
            }

            if(buffer.length() > 0) {
                buffer.append("\r\n");
            }

            if(line.endsWith(";")) {
                buffer.append(line.substring(0, line.length() - 1));
                break;
            }
            else {
                buffer.append(line);
            }
        }

        if(buffer.length() > 0) {
            String command = buffer.toString();
            buffer.setLength(0);
            return command;
        }
        return null;
    }

    /**
     * @param sql
     * @return String
     */
    public static String getAction(String sql) {
        int i = 0;
        int length = sql.length();

        while(i < length && sql.charAt(i) <= ' ') {
            i++;
        }

        int j = i;
        while(j < length && sql.charAt(j) > ' ') {
            j++;
        }

        if(j <= i) {
            return null;
        }
        return sql.substring(i, j).toLowerCase();
    }

    /**
     * @param text
     * @return boolean
     */
    public static int getEnd(String text) {
        if(text == null) {
            return -1;
        }

        char c;
        for(int i = text.length() - 1; i > -1; i--) {
            c = text.charAt(i);

            if(c > ' ') {
                if(c == ';') {
                    return i;
                }
                else {
                    return -1;
                }
            }
        }
        return -1;
    }

    /**
     * @param reader
     * @return BufferedReader
     */
    public static BufferedReader getBufferedReader(Reader reader) {
        if(reader instanceof BufferedReader) {
            return (BufferedReader)reader;
        }
        return new BufferedReader(reader, 4096);
    }

    /**
     * @param dataType
     * @return String
     */
    private static String getJavaTypeName(int dataType) {
        switch(dataType) {
            case java.sql.Types.BIT: {
                return "boolean";
            }
            case java.sql.Types.TINYINT:
            case java.sql.Types.SMALLINT:
            case java.sql.Types.INTEGER: {
                return "int";
            }
            case java.sql.Types.BIGINT: {
                return "long";
            }
            case java.sql.Types.FLOAT: {
                return "float";
            }
            case java.sql.Types.DOUBLE: {
                return "double";
            }
            case java.sql.Types.NUMERIC: {
                return "java.math.BigDecimal";
            }
            case java.sql.Types.DECIMAL: {
                return "java.math.BigDecimal";
            }
            case java.sql.Types.REAL: {
                return "float";
            }
            case java.sql.Types.CHAR:
            case java.sql.Types.VARCHAR:
            case java.sql.Types.LONGVARCHAR: {
                return "String";
            }
            case java.sql.Types.DATE:
            case java.sql.Types.TIME:
            case java.sql.Types.TIMESTAMP: {
                return "java.util.Date";
            }
            case java.sql.Types.BINARY: {
                return "byte[]";
            }
            case java.sql.Types.VARBINARY: {
                return "byte[]";
            }
            case java.sql.Types.LONGVARBINARY: {
                return "byte[]";
            }
            case java.sql.Types.NULL: {
                return "java.lang.Void";
            }
            case java.sql.Types.OTHER: {
                return "Object";
            }
            case java.sql.Types.JAVA_OBJECT: {
                return "Object";
            }
            case java.sql.Types.DISTINCT: {
                return "Object";
            }
            case java.sql.Types.STRUCT: {
                return "Object";
            }
            case java.sql.Types.ARRAY: {
                return "Object";
            }
            case java.sql.Types.BLOB: {
                return "java.io.InputStream";
            }
            case java.sql.Types.CLOB: {
                return "java.io.Reader";
            }
            case java.sql.Types.REF: {
                return "Object";
            }
            case java.sql.Types.DATALINK: {
                return "Object";
            }
            case java.sql.Types.BOOLEAN: {
                return "boolean";
            }
            case java.sql.Types.ROWID: {
                return "Object";
            }
            case java.sql.Types.NCHAR: {
                return "String";
            }
            case java.sql.Types.NVARCHAR: {
                return "String";
            }
            case java.sql.Types.LONGNVARCHAR: {
                return "String";
            }
            case java.sql.Types.NCLOB: {
                return "String";
            }
            case java.sql.Types.SQLXML: {
                return "String";
            }
            default: {
                return "Object";
            }
        }
    }

    /**
     * @param value
     * @param dataType
     * @return Object
     * @throws Exception
     */
    public static Object cast(String value, int dataType) throws Exception {
        if(value == null) {
            return null;
        }

        switch(dataType) {
            case java.sql.Types.BIT: {
                return (value.equals("true") || value.equals("1"));
            }
            case java.sql.Types.TINYINT:
            case java.sql.Types.SMALLINT:
            case java.sql.Types.INTEGER: {
                return Integer.parseInt(value);
            }
            case java.sql.Types.BIGINT: {
                return Long.parseLong(value);
            }
            case java.sql.Types.FLOAT: {
                return Float.parseFloat(value);
            }
            case java.sql.Types.DOUBLE: {
                return Double.parseDouble(value);
            }
            case java.sql.Types.NUMERIC: {
                return new java.math.BigDecimal(value.toCharArray());
            }
            case java.sql.Types.DECIMAL: {
                return new java.math.BigDecimal(value.toCharArray());
            }
            case java.sql.Types.REAL: {
                return Float.parseFloat(value);
            }
            case java.sql.Types.CHAR:
            case java.sql.Types.VARCHAR:
            case java.sql.Types.LONGVARCHAR: {
                return value;
            }
            case java.sql.Types.DATE:
            case java.sql.Types.TIME:
            case java.sql.Types.TIMESTAMP: {
                if(value.length() == "yyyy-MM-dd".length()) {
                    return new SimpleDateFormat("yyyy-MM-dd").parse(value);
                }
                else if(value.length() == "yyyy-MM-dd HH:mm:ss".length()) {
                    return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(value);
                }
                else {
                    throw new java.lang.IllegalArgumentException("bad datatime pattern: " + value);
                }
            }
            case java.sql.Types.BINARY: {
                // byte[]
                throw new java.lang.UnsupportedOperationException("unsupported data type: java.sql.Types.BINARY");
            }
            case java.sql.Types.VARBINARY: {
                // byte[]
                throw new java.lang.UnsupportedOperationException("unsupported data type: java.sql.Types.VARBINARY");
            }
            case java.sql.Types.LONGVARBINARY: {
                // byte[]
                throw new java.lang.UnsupportedOperationException("unsupported data type: java.sql.Types.LONGVARBINARY");
            }
            case java.sql.Types.NULL: {
                return null;
            }
            case java.sql.Types.OTHER: {
                throw new java.lang.UnsupportedOperationException("unsupported data type: java.sql.Types.OTHER");
            }
            case java.sql.Types.JAVA_OBJECT: {
                throw new java.lang.UnsupportedOperationException("unsupported data type: java.sql.Types.JAVA_OBJECT");
            }
            case java.sql.Types.DISTINCT: {
                throw new java.lang.UnsupportedOperationException("unsupported data type: java.sql.Types.DISTINCT");
            }
            case java.sql.Types.STRUCT: {
                throw new java.lang.UnsupportedOperationException("unsupported data type: java.sql.Types.STRUCT");
            }
            case java.sql.Types.ARRAY: {
                throw new java.lang.UnsupportedOperationException("unsupported data type: java.sql.Types.ARRAY");
            }
            case java.sql.Types.BLOB: {
                throw new java.lang.UnsupportedOperationException("unsupported data type: java.sql.Types.BLOB");
            }
            case java.sql.Types.CLOB: {
                return value;
            }
            case java.sql.Types.REF: {
                throw new java.lang.UnsupportedOperationException("unsupported data type: java.sql.Types.REF");
            }
            case java.sql.Types.DATALINK: {
                throw new java.lang.UnsupportedOperationException("unsupported data type: java.sql.Types.DATALINK");
            }
            case java.sql.Types.BOOLEAN: {
                return (value.equals("true") || value.equals("1"));
            }
            case java.sql.Types.ROWID: {
                throw new java.lang.UnsupportedOperationException("unsupported data type: java.sql.Types.XXXX");
            }
            case java.sql.Types.NCHAR: {
                return value;
            }
            case java.sql.Types.NVARCHAR: {
                return value;
            }
            case java.sql.Types.LONGNVARCHAR: {
                return value;
            }
            case java.sql.Types.NCLOB: {
                return value;
            }
            case java.sql.Types.SQLXML: {
                return value;
            }
            default: {
                throw new java.lang.UnsupportedOperationException("unsupported data type: unknown.");
            }
        }
    }

    /**
     * @param source
     * @param context
     * @return String
     */
    public static String replace(String source, Map<String, Object> context) {
        char c;
        int length = source.length();
        StringBuilder name = new StringBuilder();
        StringBuilder result = new StringBuilder(4096);

        for(int i = 0; i < length; i++) {
            c = source.charAt(i);

            if(c == '$' && i < length - 1 && source.charAt(i + 1) == '{') {
                for(i = i + 2; i < length; i++) {
                    c = source.charAt(i);

                    if(c == '}') {
                        Object value = context.get(name.toString().trim());

                        if(value != null) {
                            result.append(value.toString());
                        }
                        break;
                    }
                    else {
                        name.append(c);
                    }
                }
                name.setLength(0);
            }
            else {
                result.append(c);
            }
        }
        return result.toString();
    }

    /**
     * @param connection
     * @throws SQLException
     */
    public static void commit(Connection connection) throws SQLException {
        if(connection == null) {
            throw new SQLException("connection is null.");
        }
        connection.commit();
    }

    /**
     * @param connection
     * @throws SQLException
     */
    public static void rollback(Connection connection) throws SQLException {
        if(connection == null) {
            throw new SQLException("connection is null.");
        }
        connection.rollback();
    }

    /**
     * @param closeable
     */
    public static void close(Closeable closeable) {
        if(closeable != null) {
            try {
                closeable.close();
            }
            catch (IOException e) {
            }
        }
    }

    /**
     * @param connection
     */
    public static void close(Connection connection) {
        if(connection != null) {
            try {
                if(!connection.isClosed()) {
                    connection.close();
                }
            }
            catch(SQLException e) {
            }
        }
    }

    /**
     * @param statement
     */
    public static void close(Statement statement) {
        if(statement != null) {
            try {
                statement.close();
            }
            catch(SQLException e) {
            }
        }
    }

    /**
     * @param resultSet
     */
    public static void close(ResultSet resultSet) {
        if(resultSet != null) {
            try {
                resultSet.close();
            }
            catch(SQLException e) {
            }
        }
    }
}